Referential Integrity Constraints

Inserting and Updating

When a table contains foreign key contraints that reference a parent table, inserts and updates on a foreign key column result in Oracle running a SQL in the background that verifies the existence of the parent row. Even when you INSERT or UPDATE many rows in a single statement, the parent rows are still checked row-by-row and key-by-key. For example, inserting 1000 rows into a table with 6 foreign keys will execute an additional 6000 SQL statements behind the scenes.

There is no way to make Oracle perform these foreign key checks in bulk (eg. insert all rows, and then verify the foreign keys in one SELECT against the parent table.

One option is to disable the constraint before you perform the bulk-DML, and then re-enable it afterwards. The re-enable process will validate all rows together rather than individually, but it must validate the entire table, not just the rows you inserted/updated. Clearly this is a bad option if you are updating just a few hundred rows. The break-even point depends on the table, the environment, and the load; but it is commonly faster to disable/enable foreign keys if you are going to insert/update more than 1%-10% of the total rows in the table.

Deleting

A Foreign Key constraint can be set up so that when a row is deleted from the parent table, all dependent rows are deleted from the child table, or the foreign keys in the child table are set to NULL. Even if ON DELETE CASCADE or ON DELETE SET NULL is not used, Oracle must still check that the child table contains no dependent rows. If you have a DELETE that is running slowly, check for foreign keys in a GUI tool like TOAD, or using the following SQL

SELECT *
FROM   dba_dependencies
WHERE  referenced_name = 'MY_TABLE_NAME'
AND    type = 'TABLE'

If you find that your table is referenced by another, then there may be one of several problems:

• There is no index on the child table foreign key, forcing Oracle to do a full table scan.
• The Foreign Key is declared with ON DELETE CASCADE (or ON DELETE SET NULL), and there are a lot of rows to delete.
• The Foreign Key is declared with ON DELETE CASCADE, and the child table has further tables referencing its primary key. One of the first two problems may apply to the child-child table.
• A child table may have a Delete Trigger.

Foreign Keys are not indexed automatically. If a foreign key refers to a table where rows can be deleted, or where the primary key can be updated (God forbid), then the Foreign Key should be indexed. To find out what is really happening, trace the SQL and run it through TK*Prof.